using System;
using System.Data;
using System.Data.OleDb;
using LdExpress;
using System.IO;
using System.Web;
using MySql.Data.MySqlClient;
namespace LdExpress
{
    /// <summary>
    /// Pricing For Sales
    /// </summary>
    public class PricingRoadForSales
    {
        public long ID = 0;
        public string GoodsCode = "";
        public string GoodsNote = "";
        public long ExID = 0;
        public long ToID = 0;
        public string ContRequest1 = "";
        public string ContRequest2 = "";
        public string VolRequest1 = "";
        public string VolRequest2 = "";
        public string Payload1 = "";
        public string Payload2 = "";
        public long CustRelID = 0;
        public string Note_Request = "";
        public DateTime UpdateTimeRequest = DateTime.Now;
        public long UserIDRequest = 0;
        public long UserIDResponse = 0;
        public long TruckerID = 0;
        public string Cont20 = "";
        public string Cont40 = "";
        public int Suare = 0;
        public string Note_Response = "";
        public int Bkg = 0;
        public int Targeted = 0;
        public string Idea = "";

        //Extention Field
        public string GoodsName = "";
        public string DistrictName = "";
        public string ProvinceName = "";
        public string LocationName = "";
        public string UserRequest = "";
        public string UserResponse = "";
        public string CustRelName = "";
        public string TruckerName = "";
        public PricingRoadForSales()
        {
        }
        public PricingRoadForSales(long lID)
        {
            this.ID = lID;
        }
        public void Save(MySqlConnection con, MySqlTransaction trans)
        {
            string sqlCommand = "";
            if (ID <= 0) // new object, we insert new record to database
            {
                // build SQL statement
                sqlCommand = "Insert into tbl_PricingRoadForSales (" +
                             "GoodsCode," +
                             "GoodsNote," +
                             "ExID," +
                             "ToID," +
                             "ContRequest1," +
                             "ContRequest2," +
                             "VolRequest1," +
                             "VolRequest2," +
                             "Payload1," +
                             "Payload2," +
                             "CustRelID," +
                             "Note_Request," +
                             "UpdateTimeRequest," +
                             "UserIDRequest," +
                             "UserIDResponse," +
                             "Cont20," +
                             "Cont40," +
                             "TruckerID," +
                             "Suare," +
                             "Note_Response," +
                             "Bkg," +
                             "Targeted," +
                             "Idea) " +
                         " values(" +
                             "'" + DBModule.RefineString(GoodsCode) + "'," +
                             "'" + DBModule.RefineString(GoodsNote) + "'," +
                             ExID.ToString() + "," +
                             ToID.ToString() + "," +
                             "'" + DBModule.RefineString(ContRequest1) + "'," +
                             "'" + DBModule.RefineString(ContRequest2) + "'," +
                             "'" + DBModule.RefineString(VolRequest1) + "'," +
                             "'" + DBModule.RefineString(VolRequest2) + "'," +
                             "'" + DBModule.RefineString(Payload1) + "'," +
                             "'" + DBModule.RefineString(Payload2) + "'," +
                             CustRelID.ToString() + "," +
                             "'" + DBModule.RefineString(Note_Request) + "'," +
                             DBModule.RefineDatetime(UpdateTimeRequest, true) + "," +
                             UserIDRequest.ToString() + "," +
                             UserIDResponse.ToString() + "," +
                             "'" + DBModule.RefineString(Cont20) + "'," +
                             "'" + DBModule.RefineString(Cont40) + "'," +
                             TruckerID.ToString() + "," +
                             Suare.ToString() + "," +
                             "'" + DBModule.RefineString(Note_Response) + "'," +
                             Bkg.ToString() + "," +
                             Targeted.ToString() + "," +
                             "'" + DBModule.RefineString(Idea) + "')";
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, con, trans);
                UserObjectsLog.SaveObjectActionLog("PricingRoadForSales", "add", con, trans);                
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                sqlCommand = "Update tbl_PricingRoadForSales set " +
                             "GoodsCode='" + DBModule.RefineString(GoodsCode) + "'," +
                             "GoodsNote='" + DBModule.RefineString(GoodsNote) + "'," +
                             "ExID=" + ExID.ToString() + "," +
                             "ToID=" + ToID.ToString() + "," +
                             "ContRequest1='" + DBModule.RefineString(ContRequest1) + "'," +
                             "ContRequest2='" + DBModule.RefineString(ContRequest2) + "'," +
                             "VolRequest1='" + DBModule.RefineString(VolRequest1) + "'," +
                             "VolRequest2='" + DBModule.RefineString(VolRequest2) + "'," +
                             "Payload1='" + DBModule.RefineString(Payload1) + "'," +
                             "Payload2='" + DBModule.RefineString(Payload2) + "'," +
                             "CustRelID=" + CustRelID.ToString() + "," +
                             "Note_Request='" + DBModule.RefineString(Note_Request) + "'," +
                             "UpdateTimeRequest=" + DBModule.RefineDatetime(UpdateTimeRequest, true) + "," +
                             "UserIDRequest=" + UserIDRequest.ToString() + "," +
                             "UserIDResponse=" + UserIDResponse.ToString() + "," +
                             "Cont20='" + DBModule.RefineString(Cont20) + "'," +
                             "Cont40='" + DBModule.RefineString(Cont40) + "'," +
                             "TruckerID=" + TruckerID.ToString() + "," +
                             "Suare=" + Suare.ToString() + "," +
                             "Note_Response='" + DBModule.RefineString(Note_Response) + "'," +
                             "Bkg=" + Bkg.ToString() + "," +
                             "Targeted=" + Targeted.ToString() + "," +
                             "Idea='" + DBModule.RefineString(Idea) + "'" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, con, trans);
                UserObjectsLog.SaveObjectActionLog("PricingRoadForSales", "edit", con, trans);                
            }
        }
        public void Delete(MySqlConnection con, MySqlTransaction trans)
        {
            //Buil SQL Statement
            //Run SQL Statement
            string sqlCommand = "DELETE FROM tbl_PricingRoadForSales WHERE ID = " + this.ID.ToString() + " limit 1";
            DBModule.ExecuteNonQuery(sqlCommand, con, trans);
            UserObjectsLog.SaveObjectActionLog("PricingRoadForSales", "delete", con, trans);
        }
        public void Load(MySqlConnection con, MySqlTransaction trans)
        {
            string sqlCommand = "";
            //Build SQL Statement
            //Run SQL Statement
            sqlCommand = "SELECT tbl_PricingRoadForSales.*" +
                ", GoodsTable.Name as GoodsName, HuyenTable.Ten as DistrictName,TINHTABLE.Ten as ProvinceName, LocationTable.Name as LocationName" +
                ",UserTableRequest.UserName as UserRequest ,UserTableResponse.UserName as UserResponse " +
                ",CustRelTable.Name as CustRelName ,TRUCKER.BizName as TruckerName" +
                " FROM tbl_PricingRoadForSales " +
                " Left join tbl_Goods GoodsTable on tbl_PricingRoadForSales.GoodsCode = GoodsTable.Code " +
                " Left join tbl_Huyen HuyenTable on tbl_PricingRoadForSales.ExID = HuyenTable.ID " +
                " Left join tbl_Tinh TINHTABLE on HuyenTable.TinhID = TINHTABLE.ID " +
                " Left join tbl_Location LocationTable on tbl_PricingRoadForSales.ToID = LocationTable.ID " +
                " Left join tbl_AdminUsers UserTableRequest on tbl_PricingRoadForSales.UserIDRequest = UserTableRequest.ID " +
                " Left join tbl_AdminUsers UserTableResponse on tbl_PricingRoadForSales.UserIDResponse = UserTableResponse.ID " +
                " Left join tbl_customerrelation CustRelTable on tbl_PricingRoadForSales.CustRelID = CustRelTable.ID " +
                " left Join tbl_Trucker TRUCKER on tbl_PricingRoadForSales.TruckerID=TRUCKER.ID " +
                " WHERE tbl_PricingRoadForSales.ID = " + ID.ToString() + " limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID"))
                    ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("GoodsCode"))
                    GoodsCode = dr["GoodsCode"].ToString();
                if (!dr.IsNull("GoodsNote"))
                    GoodsNote = dr["GoodsNote"].ToString();
                if (!dr.IsNull("ExID"))
                    ExID = long.Parse(dr["ExID"].ToString());
                if (!dr.IsNull("ToID"))
                    ToID = long.Parse(dr["ToID"].ToString());
                if (!dr.IsNull("ContRequest1"))
                    ContRequest1 = dr["ContRequest1"].ToString();
                if (!dr.IsNull("ContRequest2"))
                    ContRequest2 = dr["ContRequest2"].ToString();
                if (!dr.IsNull("VolRequest1"))
                    VolRequest1 = dr["VolRequest1"].ToString();
                if (!dr.IsNull("VolRequest2"))
                    VolRequest2 = dr["VolRequest2"].ToString();
                if (!dr.IsNull("Payload1"))
                    Payload1 = dr["Payload1"].ToString();
                if (!dr.IsNull("Payload2"))
                    Payload2 = dr["Payload2"].ToString();
                if (!dr.IsNull("CustRelID"))
                    CustRelID = long.Parse(dr["CustRelID"].ToString());
                if (!dr.IsNull("Note_Request"))
                    Note_Request = dr["Note_Request"].ToString();
                if (!dr.IsNull("UpdateTimeRequest"))
                    UpdateTimeRequest = DateTime.Parse(dr["UpdateTimeRequest"].ToString());
                if (!dr.IsNull("UserIDRequest"))
                    UserIDRequest = long.Parse(dr["UserIDRequest"].ToString());
                if (!dr.IsNull("UserIDResponse"))
                    UserIDResponse = long.Parse(dr["UserIDResponse"].ToString());
                if (!dr.IsNull("Cont20"))
                    Cont20 = dr["Cont20"].ToString();
                if (!dr.IsNull("Cont40"))
                    Cont40 = dr["Cont40"].ToString();
                if (!dr.IsNull("TruckerID"))
                    TruckerID = long.Parse(dr["TruckerID"].ToString());
                if (!dr.IsNull("Suare"))
                    Suare = int.Parse(dr["Suare"].ToString());
                if (!dr.IsNull("Note_Response"))
                    Note_Response = dr["Note_Response"].ToString();
                if (!dr.IsNull("Bkg"))
                    Bkg = int.Parse(dr["Bkg"].ToString());
                if (!dr.IsNull("Targeted"))
                    Targeted = int.Parse(dr["Targeted"].ToString());
                if (!dr.IsNull("Idea"))
                    Idea = dr["Idea"].ToString();

                if (!dr.IsNull("GoodsName"))
                    GoodsName = dr["GoodsName"].ToString();
                if (!dr.IsNull("DistrictName"))
                    DistrictName = dr["DistrictName"].ToString();
                if (!dr.IsNull("ProvinceName"))
                    ProvinceName = dr["ProvinceName"].ToString();
                if (!dr.IsNull("LocationName"))
                    LocationName = dr["LocationName"].ToString();
                if (!dr.IsNull("UserRequest"))
                    UserRequest = dr["UserRequest"].ToString();
                if (!dr.IsNull("UserResponse"))
                    UserResponse = dr["UserResponse"].ToString();
                if (!dr.IsNull("CustRelName"))
                    CustRelName = dr["CustRelName"].ToString();
                if (!dr.IsNull("TruckerName"))
                    TruckerName = dr["TruckerName"].ToString();

            }
            else
            {
            }
        }
        public static void GetList(string Condition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection con, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "SELECT tbl_PricingRoadForSales.*" +
               ", GOODSTABLE.Name as GoodsName, HUYENTABLE.Ten as DistrictName,TINHTABLE.Ten as ProvinceName, LOCATIONTABLE.Name as LocationName" +
               ",USERTABLEREQUEST.UserName as UserRequest ,USERTABLERESPONSE.UserName as UserResponse " +
               ",CUSTRELTABLE.Name as CustRelName ,TRUCKER.BizName as TruckerName" +
               " FROM tbl_PricingRoadForSales " +
               " Left join tbl_Goods GOODSTABLE on tbl_PricingRoadForSales.GoodsCode = GoodsTable.Code " +
               " Left join tbl_Huyen HUYENTABLE on tbl_PricingRoadForSales.ExID = HuyenTable.ID " +
               " Left join tbl_Tinh TINHTABLE on HuyenTable.TinhID = TINHTABLE.ID " +
               " Left join tbl_Location LOCATIONTABLE on tbl_PricingRoadForSales.ToID = LocationTable.ID " +
               " Left join tbl_AdminUsers USERTABLEREQUEST on tbl_PricingRoadForSales.UserIDRequest = UserTableRequest.ID " +
               " Left join tbl_AdminUsers USERTABLERESPONSE on tbl_PricingRoadForSales.UserIDResponse = UserTableResponse.ID " +
               " Left join tbl_customerrelation CUSTRELTABLE on tbl_PricingRoadForSales.CustRelID = CustRelTable.ID " +
               " left Join tbl_Trucker TRUCKER on tbl_PricingRoadForSales.TruckerID=TRUCKER.ID " +
                 " WHERE 1=1";
            if ((Condition != "") && (Condition != null))
            {
                sqlCommand += Condition;
            }

            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(sqlCommand, NumOfItemsPerPage, PageNo, out NumberOfPages, con, trans);
            }
            else
                ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
        }
        public static void DoSearch(string condition, string OrderBy, out DataSet ds, MySqlConnection con, MySqlTransaction trans)
        {
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "Select `ID` from tbl_PricingRoadForSales where 1=1 ";
            if (condition.Length > 0)
                sqlCommand += condition;
            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
        }
    }
}
